﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;

namespace MasMembers.DAL
{
    public class MemberBasicInfoEntity
    {
        public Int32 m_Id { get; set; }
        public String m_Mem_Name { get; set; }
        public String m_Id_Type { get; set; }
        public String m_FaceBook { get; set; }
        public String m_Twitter { get; set; }
        public String m_Id_Num { get; set; }
        public DateTime m_Id_Issue_Date { get; set; }
        public String m_Id_Issue_Palce { get; set; }
        public DateTime m_DOB { get; set; }
        public String m_POB { get; set; }
        public String m_Gender { get; set; }
        public String m_Status { get; set; }
        public String m_Study { get; set; }
        public String m_Study_Year { get; set; }
        public String m_Study_Grade { get; set; }
        public String m_Study_Group { get; set; }
        public String m_Address_City { get; set; }
        public String m_Address_Place { get; set; }
        public String m_Address_Street { get; set; }
        public String m_Email { get; set; }
        public String m_Phone { get; set; }
        public String m_Mobile1 { get; set; }
        public String m_Mobile2 { get; set; }
        public String m_Blood_Type { get; set; }
        public Int32 m_Group_Id { get; set; }
        public String m_military_status { get; set; }
        public String Photo { get; set; }
        public Int32 Age { get { return DateTime.Now.Year - m_DOB.Year; } }

    }
    class MemberBasicInfo
    {
        private static string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='MasMembers.accdb';Jet OLEDB:Database Password=Yarabe11";
        private static String cmdText = "";

        public static List<String> getRegions()
        {
            List<string> regions = new List<string>();

            cmdText = "Select distinct Address_Place from MemberBasicInfo";
            OleDbConnection Con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, Con);
            try
            {
                Con.Open();
                OleDbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    if (reader["Address_Place"] != null && reader["Address_Place"].ToString() != "")
                        regions.Add(reader["Address_Place"].ToString());
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ عند البحث عن رقم العضو مطلوب من قاعدة البيانات ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }
            return regions;
        }

        public static Int32 Add(MemberBasicInfoEntity s)
        {
            Int32 id = -1;
            cmdText = "Insert into MemberBasicInfo (Mem_Name,Id_Type,FaceBook,Twitter,Id_Num,Id_Issue_Date,Id_Issue_Palce,DOB,POB,Gender,Status,Study,Study_Year,Study_Grade,Study_Group,Address_City,Address_Place,Address_Street,Email,Phone,Mobile1, Mobile2, Blood_Type ,Group_Id, Military_status, Photo) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

            OleDbConnection Con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, Con);

            cmd.Parameters.AddWithValue("@Mem_Name", s.m_Mem_Name);
            cmd.Parameters.AddWithValue("@Id_Type", s.m_Id_Type);
            cmd.Parameters.AddWithValue("@FaceBook", s.m_FaceBook);
            cmd.Parameters.AddWithValue("@Twitter", s.m_Twitter);
            cmd.Parameters.AddWithValue("@Id_Num", s.m_Id_Num);
            cmd.Parameters.AddWithValue("@Id_Issue_Date", s.m_Id_Issue_Date);
            cmd.Parameters.AddWithValue("@Id_Issue_Palce", s.m_Id_Issue_Palce);
            cmd.Parameters.AddWithValue("@DOB", s.m_DOB);
            cmd.Parameters.AddWithValue("@POB", s.m_POB);
            cmd.Parameters.AddWithValue("@Gender", s.m_Gender);
            cmd.Parameters.AddWithValue("@Status", s.m_Status);
            cmd.Parameters.AddWithValue("@Study", s.m_Study);
            cmd.Parameters.AddWithValue("@Study_Year", s.m_Study_Year);
            cmd.Parameters.AddWithValue("@Study_Grade", s.m_Study_Grade);
            cmd.Parameters.AddWithValue("@Study_Group", s.m_Study_Group);
            cmd.Parameters.AddWithValue("@Address_City", s.m_Address_City);
            cmd.Parameters.AddWithValue("@Address_Place", s.m_Address_Place);
            cmd.Parameters.AddWithValue("@Address_Street", s.m_Address_Street);
            cmd.Parameters.AddWithValue("@Email", s.m_Email);
            cmd.Parameters.AddWithValue("@Phone", s.m_Phone);
            cmd.Parameters.AddWithValue("@Mobile1", s.m_Mobile1);
            cmd.Parameters.AddWithValue("@Mobile2", s.m_Mobile2);
            cmd.Parameters.AddWithValue("@Blood_Type", s.m_Blood_Type);
            cmd.Parameters.AddWithValue("@Group_Id", s.m_Group_Id);
            cmd.Parameters.AddWithValue("@Military_status", s.m_military_status);
            cmd.Parameters.AddWithValue("@Photo", s.Photo);
            try
            {
                Con.Open();
                int Num = cmd.ExecuteNonQuery();
                if (Num != 0)
                    id = GetFullMemberEntity(s).m_Id;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + ex.StackTrace);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return id;
        }

        public static bool Update(MemberBasicInfoEntity s)
        {
            bool flag = true;

            cmdText = "Update MemberBasicInfo set Mem_Name=?, Id_Type=?, FaceBook=?, Twitter=?, Id_Num=?, Id_Issue_Date=?, Id_Issue_Palce=?, DOB=?, POB=?,Gender=?, Status=?, Study=?, Study_Year=?, Study_Grade=?,  Study_Group=?, Address_City =?, Address_Place=?, Address_Street=?,Email=?, Phone=?, Mobile1=?, Mobile2=?, Blood_Type=?, Group_Id=?,Military_status=?, Photo=?  WHERE Id= ? ";

            OleDbConnection Con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, Con);

            cmd.Parameters.AddWithValue("@Mem_Name", s.m_Mem_Name);
            cmd.Parameters.AddWithValue("@Id_Type", s.m_Id_Type);
            cmd.Parameters.AddWithValue("@FaceBook", s.m_FaceBook);
            cmd.Parameters.AddWithValue("@Twitter", s.m_Twitter);
            cmd.Parameters.AddWithValue("@Id_Num", s.m_Id_Num);
            cmd.Parameters.AddWithValue("@Id_Issue_Date", s.m_Id_Issue_Date);
            cmd.Parameters.AddWithValue("@Id_Issue_Palce", s.m_Id_Issue_Palce);
            cmd.Parameters.AddWithValue("@DOB", s.m_DOB);
            cmd.Parameters.AddWithValue("@POB", s.m_POB);
            cmd.Parameters.AddWithValue("@Gender", s.m_Gender);
            cmd.Parameters.AddWithValue("@Status", s.m_Status);
            cmd.Parameters.AddWithValue("@Study", s.m_Study);
            cmd.Parameters.AddWithValue("@Study_Year", s.m_Study_Year);
            cmd.Parameters.AddWithValue("@Study_Grade", s.m_Study_Grade);
            cmd.Parameters.AddWithValue("@Study_Group", s.m_Study_Group);
            cmd.Parameters.AddWithValue("@Address_City", s.m_Address_City);
            cmd.Parameters.AddWithValue("@Address_Place", s.m_Address_Place);
            cmd.Parameters.AddWithValue("@Address_Street", s.m_Address_Street);
            cmd.Parameters.AddWithValue("@Email", s.m_Email);
            cmd.Parameters.AddWithValue("@Phone", s.m_Phone);
            cmd.Parameters.AddWithValue("@Mobile1", s.m_Mobile1);
            cmd.Parameters.AddWithValue("@Mobile2", s.m_Mobile2);
            cmd.Parameters.AddWithValue("@Blood_Type", s.m_Blood_Type);
            cmd.Parameters.AddWithValue("@Group_Id", s.m_Group_Id);
            cmd.Parameters.AddWithValue("@Military_status", s.m_military_status);
            cmd.Parameters.AddWithValue("@Photo", s.Photo);
            cmd.Parameters.AddWithValue("@Id", s.m_Id);

            try
            {
                Con.Open();
                int num = cmd.ExecuteNonQuery();
                if (num == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ فى تعديل البيانات " + ex.Message);
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return flag;

        }

        public static bool Delete(Int32 s)
        {
            bool flag = true;

            cmdText = "Delete from MemberBasicInfo where Id = ?";

            OleDbConnection Con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, Con);

            cmd.Parameters.AddWithValue("@Id", s);

            try
            {
                Con.Open();
                int num = cmd.ExecuteNonQuery();
                if (num == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ اثناء الحذف ", "رساله ادارية " + ex.Message);
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return flag;
        }

        public static MemberBasicInfoEntity GetMemberByG_ID(Int32 Id)
        {
            MemberBasicInfoEntity x = null;

            cmdText = "Select * from MemberBasicInfo where Id= ? ";

            OleDbConnection Con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, Con);

            cmd.Parameters.AddWithValue("@Id", Id);

            try
            {
                Con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    x = FillEntityFromReader(reader);

                }

                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ عند البحث عن رقم العضو مطلوب من قاعدة البيانات ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return x;
        }

        public static MemberBasicInfoEntity GetFullMemberEntity(MemberBasicInfoEntity e)
        {

            cmdText = "Select * from MemberBasicInfo where (Mem_Name=? AND  Id_Type=?AND FaceBook=?AND Twitter=? AND  Id_Num=? AND  Id_Issue_Date=? AND  Id_Issue_Palce=? AND  DOB=? AND  POB=? AND Gender=? AND  Status=? AND  Study=? AND  Study_Year=? AND  Study_Grade=? AND   Study_Group=? AND  Address_City =? AND  Address_Place=? AND  Address_Street=? AND Email=? AND  Phone=? AND  Mobile1=? AND  Mobile2=? AND  Blood_Type=? AND  Group_Id=?)";

            OleDbConnection Con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, Con);

            cmd.Parameters.AddWithValue("@Mem_Name", e.m_Mem_Name);
            cmd.Parameters.AddWithValue("@Id_Type", e.m_Id_Type);
            cmd.Parameters.AddWithValue("@FaceBook", e.m_FaceBook);
            cmd.Parameters.AddWithValue("@Twitter", e.m_Twitter);
            cmd.Parameters.AddWithValue("@Id_Num", e.m_Id_Num);
            cmd.Parameters.AddWithValue("@Id_Issue_Date", e.m_Id_Issue_Date);
            cmd.Parameters.AddWithValue("@Id_Issue_Palce", e.m_Id_Issue_Palce);
            cmd.Parameters.AddWithValue("@DOB", e.m_DOB);
            cmd.Parameters.AddWithValue("@POB", e.m_POB);
            cmd.Parameters.AddWithValue("@Gender", e.m_Gender);
            cmd.Parameters.AddWithValue("@Status", e.m_Status);
            cmd.Parameters.AddWithValue("@Study", e.m_Study);
            cmd.Parameters.AddWithValue("@Study_Year", e.m_Study_Year);
            cmd.Parameters.AddWithValue("@Study_Grade", e.m_Study_Grade);
            cmd.Parameters.AddWithValue("@Study_Group", e.m_Study_Group);
            cmd.Parameters.AddWithValue("@Address_City", e.m_Address_City);
            cmd.Parameters.AddWithValue("@Address_Place", e.m_Address_Place);
            cmd.Parameters.AddWithValue("@Address_Street", e.m_Address_Street);
            cmd.Parameters.AddWithValue("@Email", e.m_Email);
            cmd.Parameters.AddWithValue("@Phone", e.m_Phone);
            cmd.Parameters.AddWithValue("@Mobile1", e.m_Mobile1);
            cmd.Parameters.AddWithValue("@Mobile2", e.m_Mobile2);
            cmd.Parameters.AddWithValue("@Blood_Type", e.m_Blood_Type);
            cmd.Parameters.AddWithValue("@Group_Id", e.m_Group_Id);

            try
            {
                Con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    e.m_Id = Int16.Parse(reader["Id"].ToString());
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(" خطأ عند تحديد رقم العميل المطلوب من قاعدة بيانات العميل ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();

            }

            return e;
        }

        public static List<MemberBasicInfoEntity> GetAllMembers()
        {
            {
                MemberBasicInfoEntity x = null;
                List<MemberBasicInfoEntity> MemberList = new List<MemberBasicInfoEntity>();

                cmdText = "Select * from MemberBasicInfo   ";

                OleDbConnection Con = new OleDbConnection(conString);
                OleDbCommand cmd = new OleDbCommand(cmdText, Con);

                try
                {
                    Con.Open();

                    OleDbDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        x = FillEntityFromReader(reader);
                        MemberList.Add(x);
                    }

                    reader.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("خطأ عند تحديد كل البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
                }
                finally
                {
                    if (Con.State == ConnectionState.Open)
                        Con.Close();
                }

                return MemberList;
            }
        }

        public static List<MemberBasicInfoEntity> GetAllMembersFilteringByGroup(Int32 groupId)
        {
            MemberBasicInfoEntity x = null;
            List<MemberBasicInfoEntity> MemberList = new List<MemberBasicInfoEntity>();

            cmdText = "Select * from MemberBasicInfo  where Group_Id=? ";

            OleDbConnection Con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, Con);
            cmd.Parameters.AddWithValue("@Group_Id", groupId);

            try
            {
                Con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    x = FillEntityFromReader(reader);
                    MemberList.Add(x);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ عند تحديد كل البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return MemberList;
        }

        public static List<MemberBasicInfoEntity> GetMembersByNameFilteringByName(String name, Int32 groupId)
        {
            MemberBasicInfoEntity x = null;
            List<MemberBasicInfoEntity> MemberList = new List<MemberBasicInfoEntity>();

            cmdText = "Select * from MemberBasicInfo  Where (Mem_Name LIKE ? AND Group_Id=?)";

            OleDbConnection Con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, Con);
            cmd.Parameters.AddWithValue("@Mem_Name", name);
            cmd.Parameters.AddWithValue("@Group_Id", groupId);

            try
            {
                Con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    x = FillEntityFromReader(reader);
                    MemberList.Add(x);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ عند تحديد كل البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return MemberList;
        }

        public static List<MemberBasicInfoEntity> GetMembersByName(String name)
        {
            MemberBasicInfoEntity x = null;
            List<MemberBasicInfoEntity> MemberList = new List<MemberBasicInfoEntity>();

            cmdText = "Select * from MemberBasicInfo  Where Mem_Name LIKE ? ";

            OleDbConnection Con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, Con);
            cmd.Parameters.AddWithValue("@Mem_Name", name);
            try
            {
                Con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    x = FillEntityFromReader(reader);
                    MemberList.Add(x);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ عند تحديد كل البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return MemberList;
        }

        public static List<String> GetMilitaryStatusValues()
        {

            List<String> list = new List<String>();
            cmdText = "Select distinct (Military_status) from MemberBasicInfo";

            OleDbConnection Con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, Con);
            try
            {
                Con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    if (reader["Military_status"] != null || String.IsNullOrEmpty(reader["Military_status"].ToString()))
                        list.Add(reader["Military_status"].ToString());
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ عند تحديد كل البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }
            return list;
        }
        private static MemberBasicInfoEntity FillEntityFromReader(OleDbDataReader reader)
        {
            MemberBasicInfoEntity x = new MemberBasicInfoEntity();
            x.m_Id = Int32.Parse(reader["Id"].ToString());
            if (reader["Mem_Name"] != null)
                x.m_Mem_Name = reader["Mem_Name"].ToString();
            if ((reader["Id_Type"] != null))
                x.m_Id_Type = reader["Id_Type"].ToString();
            if (reader["FaceBook"] != null)
                x.m_FaceBook = reader["FaceBook"].ToString();
            if (reader["Twitter"] != null)
                x.m_Twitter = reader["Twitter"].ToString();
            if (reader["Id_Num"] != null)
                x.m_Id_Num = reader["Id_Num"].ToString();
            if (reader["Id_Issue_Date"] != null)
                x.m_Id_Issue_Date = (DateTime)reader["Id_Issue_Date"];
            if (reader["Id_Issue_Palce"] != null)
                x.m_Id_Issue_Palce = reader["Id_Issue_Palce"].ToString();
            if (reader["DOB"] != null)
                x.m_DOB = (DateTime)reader["DOB"];
            if (reader["POB"] != null)
                x.m_POB = reader["POB"].ToString();
            if (reader["Gender"] != null)
                x.m_Gender = reader["Gender"].ToString();
            if (reader["Status"] != null)
                x.m_Status = reader["Status"].ToString();
            if (reader["Study"] != null)
                x.m_Study = reader["Study"].ToString();
            if (reader["Study_Year"] != null)
                x.m_Study_Year = reader["Study_Year"].ToString();
            if (reader["Study_Grade"] != null)
                x.m_Study_Grade = reader["Study_Grade"].ToString();
            if (reader["Study_Group"] != null)
                x.m_Study_Group = reader["Study_Group"].ToString();
            if (reader["Address_City"] != null)
                x.m_Address_City = reader["Address_City"].ToString();
            if (reader["Address_Place"] != null)
                x.m_Address_Place = reader["Address_Place"].ToString();
            if (reader["Address_Street"] != null)
                x.m_Address_Street = reader["Address_Street"].ToString();
            if (reader["Military_status"] != null)
                x.m_military_status = reader["Military_status"].ToString();
            if (reader["Email"] != null)
                x.m_Email = reader["Email"].ToString();
            if (reader["Phone"] != null)
                x.m_Phone = reader["Phone"].ToString();
            if (reader["Mobile1"] != null)
                x.m_Mobile1 = reader["Mobile1"].ToString();
            if (reader["Mobile2"] != null)
                x.m_Mobile2 = reader["Mobile2"].ToString();
            if (reader["Blood_Type"] != null)
                x.m_Blood_Type = reader["Blood_Type"].ToString();
            if (reader["Group_Id"] != null)
                x.m_Group_Id = Int32.Parse(reader["Group_Id"].ToString());
            if (reader["Photo"] != null)
                x.Photo = reader["Photo"].ToString();
            return x;
        }
    }
}

